﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using RTracker.Models.Common;
using RTracker.Models.ErrorLog;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;

namespace RTracker.Areas.Recruitment.Models.CandidateDrive
{
    public class MCandidateDrive
    {
        
        /// static variable RowSize from config settings to get list of this row size.
        public static int nRowSize = Convert.ToInt32(ConfigurationManager.AppSettings["TotalRecordsInListUser"]);

        /// <summary>
        /// This method is used to create a user in the database.
        /// </summary>
        /// <param name="objCAuthenticationContext">This object contains information about user's authentication context.</param>
        /// <param name="objCCandidateDrive">This object contains details of the user.</param>
        /// <returns>CStatus which is an object that contain transaction details</returns>
        public CStatus CreateCandidateDrive(CAuthenticationContext objCAuthenticationContext, ref CCandidateDrive objCCandidateDrive)
        {
            CStatus oCStatus = new CStatus();
            if (!CCommonCollection.ConnectionString.Equals(string.Empty))
            {
                using (SqlConnection con = new SqlConnection(CCommonCollection.ConnectionString))
                {
                    try
                    {
                        con.Open();
                        SqlCommand cmd = new SqlCommand("r_create_candidate_drive_procedure", con);
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.Add("@organization_id", SqlDbType.Int).Value = objCCandidateDrive.OrganizationId;
                        cmd.Parameters.Add("@candidate_drive_desc", SqlDbType.NVarChar).Value = objCCandidateDrive.CandidateDriveDesc;
                        cmd.Parameters.Add("@candidate", SqlDbType.Int).Value = objCCandidateDrive.Candidate;
                        cmd.Parameters.Add("@vacancy", SqlDbType.Int).Value = objCCandidateDrive.Vacancy;
                        cmd.Parameters.Add("@department", SqlDbType.Int).Value = objCCandidateDrive.Department;
                        cmd.Parameters.Add("@start_date", SqlDbType.DateTime).Value = objCCandidateDrive.StartDate;
                        cmd.Parameters.Add("@status", SqlDbType.Int).Value = objCCandidateDrive.Status;
                        cmd.Parameters.Add("@end_date", SqlDbType.DateTime).Value = objCCandidateDrive.EndDate;
                        cmd.Parameters.Add("@rounds", SqlDbType.NVarChar).Value = objCCandidateDrive.Rounds;
                        cmd.Parameters.Add("@created_by", SqlDbType.Int).Value = objCCandidateDrive.CreatedBy;

                        cmd.Parameters.Add("@idOut", SqlDbType.Int).Value = null;
                        cmd.Parameters["@idOut"].Direction = ParameterDirection.Output;
                        cmd.Parameters.Add("@error", SqlDbType.Int).Value = null;
                        cmd.Parameters["@error"].Direction = ParameterDirection.Output;

                        cmd.ExecuteNonQuery();

                        if (Convert.ToInt32(cmd.Parameters["@idOut"].Value.ToString()) > 0)
                        {
                            oCStatus.Success = true;
                            oCStatus.StatusDetails = CAppConstants.SessionSuccess;
                        }
                        else
                        {
                            oCStatus.Success = false;
                            oCStatus.StatusDetails = Convert.ToInt32(cmd.Parameters["@idOut"].Value.ToString()).DbError();
                            //oCStatus.StatusDetails = CAppErrorsConstants.SomethingWentWrong;
                        }
                    }
                    catch (Exception ex)
                    {
                        oCStatus.Success = false;
                        oCStatus.StatusDetails = ex.Message;
                    }
                }
            }
            else
            {
                oCStatus.Success = false;
                oCStatus.StatusDetails = CAppErrorsConstants.ConStringNotFound;
            }

            return oCStatus;
        }

        /// <summary>
        /// This method is used to delete a user in the database.
        /// </summary>
        /// <param name="objCAuthenticationContext">This object contains information about user's authentication context.</param>
        /// <param name="objCCandidateDrive">This object contains details of the user.</param>
        /// <returns>CStatus which is an object that contain transaction details.</returns>
        public CStatus DeleteCandidateDrive(CAuthenticationContext objCAuthenticationContext, ref CCandidateDrive objCCandidateDrive)
        {
            CStatus oCStatus = new CStatus();
            if (!CCommonCollection.ConnectionString.Equals(string.Empty))
            {
                using (SqlConnection con = new SqlConnection(CCommonCollection.ConnectionString))
                {
                    try
                    {
                        con.Open();
                        SqlCommand cmd = new SqlCommand("r_delete_candidate_drive_procedure", con);
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.Add("@created_by", SqlDbType.Int).Value = objCAuthenticationContext.UserId;
                        cmd.Parameters.Add("@candidate_drive_ids", SqlDbType.NVarChar).Value = objCCandidateDrive.CandidateDriveIds;

                        cmd.Parameters.Add("@idOut", SqlDbType.Int).Value = null;
                        cmd.Parameters["@idOut"].Direction = ParameterDirection.Output;
                        cmd.Parameters.Add("@error", SqlDbType.Int).Value = null;
                        cmd.Parameters["@error"].Direction = ParameterDirection.Output;

                        cmd.ExecuteNonQuery();

                        if (Convert.ToInt32(cmd.Parameters["@idOut"].Value.ToString()) > 0)
                        {
                            oCStatus.Success = true;
                            oCStatus.StatusDetails = CAppConstants.SessionSuccess;
                        }
                        else
                        {
                            oCStatus.Success = false;
                            oCStatus.StatusDetails = Convert.ToInt32(cmd.Parameters["@idOut"].Value.ToString()).DbError();
                            //oCStatus.StatusDetails = CAppErrorsConstants.SomethingWentWrong;
                        }
                    }
                    catch (Exception ex)
                    {
                        oCStatus.Success = false;
                        oCStatus.StatusDetails = ex.Message;
                    }
                }
            }
            else
            {
                oCStatus.Success = false;
                oCStatus.StatusDetails = CAppErrorsConstants.ConStringNotFound;
            }

            return oCStatus;
        }

        /// <summary>
        /// This method is used to update the user's detail in the database.
        /// </summary>
        /// <param name="objCAuthenticationContext">This object contains information about user's authentication context.</param>
        /// <param name="objCCandidateDrive">This object contains details of the user.</param>
        /// <returns>CStatus which is an object that contain transaction details.</returns>
        public CStatus UpdateCandidateDrive(CAuthenticationContext objCAuthenticationContext, ref CCandidateDrive objCCandidateDrive)
        {
            CStatus oCStatus = new CStatus();
            if (!CCommonCollection.ConnectionString.Equals(string.Empty))
            {
                using (SqlConnection con = new SqlConnection(CCommonCollection.ConnectionString))
                {
                    try
                    {
                        con.Open();
                        SqlCommand cmd = new SqlCommand("r_update_candidate_drive_procedure", con);
                        cmd.CommandType = CommandType.StoredProcedure;

                        cmd.Parameters.Add("@candidate_drive_id", SqlDbType.BigInt).Value = objCCandidateDrive.CandidateDriveId;
                        cmd.Parameters.Add("@organization_id", SqlDbType.Int).Value = objCCandidateDrive.OrganizationId;
                        cmd.Parameters.Add("@candidate_drive_desc", SqlDbType.NVarChar).Value = objCCandidateDrive.CandidateDriveDesc;
                        cmd.Parameters.Add("@candidate", SqlDbType.Int).Value = objCCandidateDrive.Candidate;
                        cmd.Parameters.Add("@vacancy", SqlDbType.Int).Value = objCCandidateDrive.Vacancy;
                        cmd.Parameters.Add("@department", SqlDbType.Int).Value = objCCandidateDrive.Department;
                        cmd.Parameters.Add("@start_date", SqlDbType.DateTime).Value = objCCandidateDrive.StartDate;
                        cmd.Parameters.Add("@status", SqlDbType.Int).Value = objCCandidateDrive.Status;
                        cmd.Parameters.Add("@end_date", SqlDbType.DateTime).Value = objCCandidateDrive.EndDate;
                        cmd.Parameters.Add("@rounds", SqlDbType.NVarChar).Value = objCCandidateDrive.Rounds;                        
                        cmd.Parameters.Add("@created_by", SqlDbType.Int).Value = objCCandidateDrive.CreatedBy;

                        cmd.Parameters.Add("@idOut", SqlDbType.Int).Value = null;
                        cmd.Parameters["@idOut"].Direction = ParameterDirection.Output;
                        cmd.Parameters.Add("@error", SqlDbType.Int).Value = null;
                        cmd.Parameters["@error"].Direction = ParameterDirection.Output;

                        cmd.ExecuteNonQuery();

                        if (Convert.ToInt32(cmd.Parameters["@idOut"].Value.ToString()) > 0)
                        {
                            oCStatus.Success = true;
                            oCStatus.StatusDetails = CAppConstants.SessionSuccess;
                        }
                        else
                        {
                            oCStatus.Success = false;
                            oCStatus.StatusDetails = Convert.ToInt32(cmd.Parameters["@idOut"].Value.ToString()).DbError();
                            //oCStatus.StatusDetails = CAppErrorsConstants.SomethingWentWrong;
                        }
                    }
                    catch (Exception ex)
                    {
                        oCStatus.Success = false;
                        oCStatus.StatusDetails = ex.Message;
                    }
                }
            }
            else
            {
                oCStatus.Success = false;
                oCStatus.StatusDetails = CAppErrorsConstants.ConStringNotFound;
            }

            return oCStatus;
        }

        /// <summary>
        /// This method is used to get the user's detail in the database.
        /// </summary>
        /// <param name="objCAuthenticationContext">This object contains information about user's authentication context.</param>
        /// <param name="objCCandidateDrive">This object contains details of the user.</param>
        /// <returns>CStatus which is an object that contain transaction details.</returns>
        public CStatus GetCandidateDriveDetail(CAuthenticationContext objCAuthenticationContext, ref CCandidateDrive objCCandidateDrive)
        {
            CStatus oCStatus = new CStatus();
            if (!CCommonCollection.ConnectionString.Equals(string.Empty))
            {
                using (SqlConnection con = new SqlConnection(CCommonCollection.ConnectionString))
                {
                    try
                    {
                        SqlCommand cmd = new SqlCommand("r_get_candidate_drive_detail_procedure", con);
                        cmd.CommandType = CommandType.StoredProcedure;

                        cmd.Parameters.Add("@candidate_drive_id", SqlDbType.NVarChar).Value = objCCandidateDrive.CandidateDriveId;
                        cmd.Parameters.Add("@idOut", SqlDbType.Int).Value = null;
                        cmd.Parameters["@idOut"].Direction = ParameterDirection.Output;

                        SqlDataAdapter adp = new SqlDataAdapter(cmd);

                        DataTable dt = new DataTable();

                        int rowsAffected = adp.Fill(dt);

                        if (rowsAffected > 0)
                        {
                            objCCandidateDrive = dt.ConvertToListFromDataTableCandidateDrive()[0];
                        }

                        if (Convert.ToInt32(cmd.Parameters["@idOut"].Value.ToString()) > 0)
                        {
                            oCStatus.Success = true;
                            oCStatus.StatusDetails = CAppConstants.DetailSuccess;
                        }
                        else
                        {
                            oCStatus.Success = false;
                            oCStatus.StatusDetails = Convert.ToInt32(cmd.Parameters["@idOut"].Value.ToString()).DbError();
                        }
                    }
                    catch (Exception ex)
                    {
                        oCStatus.Success = false;
                        oCStatus.StatusDetails = ex.Message;
                    }
                }
            }
            else
            {
                oCStatus.Success = false;
                oCStatus.StatusDetails = CAppErrorsConstants.ConStringNotFound;
            }

            return oCStatus;
        }


        #region Soft & Hard Lock Mng Code
        ///// <summary>
        ///// Sets the softlock on the record whose Id is passed.
        ///// </summary>
        ///// <param name="objCAuthenticationContext">This object contains information about user's authentication context.</param>
        ///// <param name="objCCandidateDrive">This object contains details of the user.</param>
        ///// <returns>CStatus which is an object that contain transaction details.</returns>
        public CStatus SetCandidateDriveSoftLock(CAuthenticationContext objCAuthenticationContext, CCandidateDrive objCCandidateDrive)
        {
            CStatus oCStatus = new CStatus();
            if (!CCommonCollection.ConnectionString.Equals(string.Empty))
            {
                using (SqlConnection con = new SqlConnection(CCommonCollection.ConnectionString))
                {
                    try
                    {
                        con.Open();
                        SqlCommand cmd = new SqlCommand("r_set_candidate_drive_soft_lock_procedure", con);
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.Add("@candidate_drive_id", SqlDbType.Int).Value = objCCandidateDrive.CandidateDriveId;
                        cmd.Parameters.Add("@created_by", SqlDbType.Int).Value = objCAuthenticationContext.UserId;

                        cmd.Parameters.Add("@idOut", SqlDbType.Int).Value = null;
                        cmd.Parameters["@idOut"].Direction = ParameterDirection.Output;
                        cmd.Parameters.Add("@error", SqlDbType.Int).Value = null;
                        cmd.Parameters["@error"].Direction = ParameterDirection.Output;

                        cmd.ExecuteNonQuery();

                        if (Convert.ToInt32(cmd.Parameters["@idOut"].Value.ToString()) > 0)
                        {
                            oCStatus.Success = true;
                            oCStatus.StatusDetails = CAppConstants.SessionSuccess;
                        }
                        else
                        {
                            oCStatus.Success = false;
                            oCStatus.StatusDetails = Convert.ToInt32(cmd.Parameters["@idOut"].Value.ToString()).DbError();
                            //oCStatus.StatusDetails = CAppErrorsConstants.SomethingWentWrong;
                        }
                    }
                    catch (Exception ex)
                    {
                        oCStatus.Success = false;
                        oCStatus.StatusDetails = ex.Message;
                    }
                }
            }
            else
            {
                oCStatus.Success = false;
                oCStatus.StatusDetails = CAppErrorsConstants.ConStringNotFound;
            }

            return oCStatus;
        }


        /// <summary>
        /// Sets the hardlock on the record whose Id is passed.
        /// </summary>
        /// <param name="objCAuthenticationContext">This object contains information about user's authentication context.</param>
        /// <param name="objCCandidateDrive">This object contains details of the user.</param>
        /// <returns>CStatus which is an object that contain transaction details.</returns>
        public CStatus SetCandidateDriveHardLock(CAuthenticationContext objCAuthenticationContext, CCandidateDrive objCCandidateDrive)
        {
            CStatus oCStatus = new CStatus();
            if (!CCommonCollection.ConnectionString.Equals(string.Empty))
            {
                using (SqlConnection con = new SqlConnection(CCommonCollection.ConnectionString))
                {
                    try
                    {
                        con.Open();
                        SqlCommand cmd = new SqlCommand("r_set_candidate_drive_hard_lock_procedure", con);
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.Add("@candidate_drive_id", SqlDbType.Int).Value = objCCandidateDrive.CandidateDriveId;
                        cmd.Parameters.Add("@created_by", SqlDbType.Int).Value = objCAuthenticationContext.UserId;

                        cmd.Parameters.Add("@idOut", SqlDbType.Int).Value = null;
                        cmd.Parameters["@idOut"].Direction = ParameterDirection.Output;
                        cmd.Parameters.Add("@error", SqlDbType.Int).Value = null;
                        cmd.Parameters["@error"].Direction = ParameterDirection.Output;

                        cmd.ExecuteNonQuery();

                        if (Convert.ToInt32(cmd.Parameters["@idOut"].Value.ToString()) > 0)
                        {
                            oCStatus.Success = true;
                            oCStatus.StatusDetails = CAppConstants.SessionSuccess;
                        }
                        else
                        {
                            oCStatus.Success = false;
                            oCStatus.StatusDetails = Convert.ToInt32(cmd.Parameters["@idOut"].Value.ToString()).DbError();
                            //oCStatus.StatusDetails = CAppErrorsConstants.SomethingWentWrong;
                        }
                    }
                    catch (Exception ex)
                    {
                        oCStatus.Success = false;
                        oCStatus.StatusDetails = ex.Message;
                    }
                }
            }
            else
            {
                oCStatus.Success = false;
                oCStatus.StatusDetails = CAppErrorsConstants.ConStringNotFound;
            }

            return oCStatus;
        }


        /// <summary>
        /// Releases the softlock on the record whose Id is passed.
        /// </summary>
        /// <param name="objCAuthenticationContext">This object contains information about user's authentication context.</param>
        /// <param name="objCCandidateDrive">This object contains details of the user.</param>
        /// <returns>CStatus which is an object that contain transaction details.</returns>
        public CStatus ReleaseCandidateDriveSoftLock(CAuthenticationContext objCAuthenticationContext, CCandidateDrive objCCandidateDrive)
        {
            CStatus oCStatus = new CStatus();
            if (!CCommonCollection.ConnectionString.Equals(string.Empty))
            {
                using (SqlConnection con = new SqlConnection(CCommonCollection.ConnectionString))
                {
                    try
                    {
                        con.Open();
                        SqlCommand cmd = new SqlCommand("r_release_candidate_drive_soft_lock_procedure", con);
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.Add("@candidate_drive_id", SqlDbType.Int).Value = objCCandidateDrive.CandidateDriveId;
                        cmd.Parameters.Add("@created_by", SqlDbType.Int).Value = objCAuthenticationContext.UserId;

                        cmd.Parameters.Add("@idOut", SqlDbType.Int).Value = null;
                        cmd.Parameters["@idOut"].Direction = ParameterDirection.Output;
                        cmd.Parameters.Add("@error", SqlDbType.Int).Value = null;
                        cmd.Parameters["@error"].Direction = ParameterDirection.Output;

                        cmd.ExecuteNonQuery();

                        if (Convert.ToInt32(cmd.Parameters["@idOut"].Value.ToString()) > 0)
                        {
                            oCStatus.Success = true;
                            oCStatus.StatusDetails = CAppConstants.SessionSuccess;
                        }
                        else
                        {
                            oCStatus.Success = false;
                            oCStatus.StatusDetails = Convert.ToInt32(cmd.Parameters["@idOut"].Value.ToString()).DbError();
                            //oCStatus.StatusDetails = CAppErrorsConstants.SomethingWentWrong;
                        }
                    }
                    catch (Exception ex)
                    {
                        oCStatus.Success = false;
                        oCStatus.StatusDetails = ex.Message;
                    }
                }
            }
            else
            {
                oCStatus.Success = false;
                oCStatus.StatusDetails = CAppErrorsConstants.ConStringNotFound;
            }

            return oCStatus;
        }

        /// <summary>
        /// Releases the hardlock on the record whose Id is passed.
        /// </summary>
        /// <param name="objCAuthenticationContext">This object contains information about user's authentication context.</param>
        /// <param name="objCCandidateDrive">This object contains details of the user.</param>
        /// <returns>CStatus which is an object that contain transaction details.</returns>
        public CStatus ReleaseCandidateDriveHardLock(CAuthenticationContext objCAuthenticationContext, CCandidateDrive objCCandidateDrive)
        {
            CStatus oCStatus = new CStatus();
            if (!CCommonCollection.ConnectionString.Equals(string.Empty))
            {
                using (SqlConnection con = new SqlConnection(CCommonCollection.ConnectionString))
                {
                    try
                    {
                        con.Open();
                        SqlCommand cmd = new SqlCommand("r_release_candidate_drive_hard_lock_procedure", con);
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.Add("@candidate_drive_id", SqlDbType.Int).Value = objCCandidateDrive.CandidateDriveId;
                        cmd.Parameters.Add("@created_by", SqlDbType.Int).Value = objCAuthenticationContext.UserId;

                        cmd.Parameters.Add("@idOut", SqlDbType.Int).Value = null;
                        cmd.Parameters["@idOut"].Direction = ParameterDirection.Output;
                        cmd.Parameters.Add("@error", SqlDbType.Int).Value = null;
                        cmd.Parameters["@error"].Direction = ParameterDirection.Output;

                        cmd.ExecuteNonQuery();

                        if (Convert.ToInt32(cmd.Parameters["@idOut"].Value.ToString()) > 0)
                        {
                            oCStatus.Success = true;
                            oCStatus.StatusDetails = CAppConstants.SessionSuccess;
                        }
                        else
                        {
                            oCStatus.Success = false;
                            oCStatus.StatusDetails = Convert.ToInt32(cmd.Parameters["@idOut"].Value.ToString()).DbError();
                            //oCStatus.StatusDetails = CAppErrorsConstants.SomethingWentWrong;
                        }
                    }
                    catch (Exception ex)
                    {
                        oCStatus.Success = false;
                        oCStatus.StatusDetails = ex.Message;
                    }
                }
            }
            else
            {
                oCStatus.Success = false;
                oCStatus.StatusDetails = CAppErrorsConstants.ConStringNotFound;
            }

            return oCStatus;
        } 
        #endregion

        /// <summary>
        ///  This function is used to get complete User list
        /// </summary>
        /// <returns>List of type CCandidateDrive</returns>
        public GCandidateDrive GetCandidateDriveGridList(CCandidateDrive objCCandidateDrive, CAuthenticationContext objCAuthenticationContext)
        {
            GCandidateDrive objGCandidateDrive = new GCandidateDrive();
            int nTotalRecords = 0;
            int nCurrentRowSize = nRowSize;

            if (objCCandidateDrive.RowSize != 0)
            {
                nCurrentRowSize = objCCandidateDrive.RowSize;
            }

            try
            {
                string connectionstring = CCommonCollection.ConnectionString;
                SqlConnection con = new SqlConnection(connectionstring);
                SqlCommand cmd = new SqlCommand("r_get_candidate_drive_paging_list_procedure", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@candidate_drive_id", SqlDbType.Int).Value = objCCandidateDrive.CandidateDriveId;
                cmd.Parameters.Add("@organization_id", SqlDbType.Int).Value = objCCandidateDrive.OrganizationId;
                cmd.Parameters.Add("@page_number", SqlDbType.Int).Value = objCCandidateDrive.PageNumber;
                cmd.Parameters.Add("@row_size", SqlDbType.Int).Value = nCurrentRowSize;
                cmd.Parameters.Add("@sort_on", SqlDbType.VarChar).Value = objCCandidateDrive.SortOn;
                cmd.Parameters.Add("@sort_type", SqlDbType.VarChar).Value = objCCandidateDrive.SortType;
                cmd.Parameters.Add("@idOut", SqlDbType.Int).Value = null;
                cmd.Parameters["@idOut"].Direction = ParameterDirection.Output;
                cmd.Parameters.Add("@error", SqlDbType.Int).Value = null;
                cmd.Parameters["@error"].Direction = ParameterDirection.Output;
                SqlDataAdapter adp = new SqlDataAdapter(cmd);

                DataTable dt = new DataTable();

                int rowsAffected = adp.Fill(dt);

                if (rowsAffected > 0)
                {
                    objGCandidateDrive.LstCandidateDrive = dt.ConvertToListFromDataTableCandidateDrive();
                }

                objGCandidateDrive.LstCandidateDrive[0].PageNumber = int.Parse(cmd.Parameters["@idOut"].Value.ToString());

                nTotalRecords = int.Parse(cmd.Parameters["@error"].Value.ToString());

                objGCandidateDrive.Paging = new CPaging();
                objGCandidateDrive.Paging.TotalRecordsCount = nTotalRecords;
                objGCandidateDrive.Paging.PageNumber = objGCandidateDrive.LstCandidateDrive[0].PageNumber;
                objGCandidateDrive.Paging.PageSize = nCurrentRowSize;

                if (null != objGCandidateDrive.LstCandidateDrive)
                {
                    objGCandidateDrive.Status = new CStatus();
                    objGCandidateDrive.Status.Success = true;
                }
                else
                {
                    objGCandidateDrive.Status = new CStatus();
                    objGCandidateDrive.Status.Success = false;
                    objGCandidateDrive.Status.StatusDetails = "";
                }
            }
            catch (Exception ex)
            {
                objGCandidateDrive.Status = new CStatus();
                objGCandidateDrive.Status.Success = false;
                objGCandidateDrive.Status.StatusDetails = ex.Message;
            }
            return objGCandidateDrive;
        } 
                
        /// /// <summary>
        ///  This function is used to get complete User list working under a same organization.
        /// </summary>
        /// <param name="objCAuthenticationContext">This object contains information about user's authentication context.</param>
        /// <param name="objCCandidateDrive">This object contains details of the user.</param>
        /// <param name="lstCCandidateDrive">"out" the list of users</param>
        /// <returns>CStatus which is an object that contain transaction details.</returns>
        public CStatus GetCandidateDriveList(CAuthenticationContext objCAuthenticationContext, CCandidateDrive objCCandidateDrive, out List<CCandidateDrive> lstCCandidateDrive)
        {
            CStatus oCStatus = new CStatus();
            lstCCandidateDrive = new List<CCandidateDrive>();
            if (!CCommonCollection.ConnectionString.Equals(string.Empty))
            {
                using (SqlConnection con = new SqlConnection(CCommonCollection.ConnectionString))
                {
                    try
                    {
                        con.Open();
                        SqlCommand cmd = new SqlCommand("r_get_candidate_drive_list_procedure", con);
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.Add("@organization_id", SqlDbType.Int).Value = objCCandidateDrive.OrganizationId;

                        cmd.Parameters.Add("@idOut", SqlDbType.Int).Value = null;
                        cmd.Parameters["@idOut"].Direction = ParameterDirection.Output;
                        SqlDataAdapter adp = new SqlDataAdapter(cmd);

                        DataTable dt = new DataTable();

                        int rowsAffected = adp.Fill(dt);

                        if (rowsAffected > 0)
                        {
                            lstCCandidateDrive.AddRange(dt.ConvertToListFromDataTableCandidateDrive());
                        }

                        if (Convert.ToInt32(cmd.Parameters["@idOut"].Value.ToString()) > 0)
                        {
                            oCStatus.Success = true;
                            oCStatus.StatusDetails = CAppConstants.SessionSuccess;
                        }
                        else
                        {
                            oCStatus.Success = false;
                            oCStatus.StatusDetails = Convert.ToInt32(cmd.Parameters["@idOut"].Value.ToString()).DbError();
                            //oCStatus.StatusDetails = CAppErrorsConstants.SomethingWentWrong;
                        }
                    }
                    catch (Exception ex)
                    {
                        oCStatus.Success = false;
                        oCStatus.StatusDetails = ex.Message;
                    }
                }
            }
            else
            {
                oCStatus.Success = false;
                oCStatus.StatusDetails = CAppErrorsConstants.ConStringNotFound;
            }

            return oCStatus;
        }


    }
    public static class MCandidateDriveConverter
    {
        private static Boolean ColContain(DataTable dt, DataRow dtRow, string colname)
        {
            Boolean res = false;
            if (dt.Columns.Contains(colname) && dtRow[colname] != DBNull.Value)
            {
                res = true;
            }
            return res;
        }
        public static List<CCandidateDrive> ConvertToListFromDataTableCandidateDrive(this DataTable dt)
        {

            List<CCandidateDrive> lstCUser = new List<CCandidateDrive>();

            foreach (DataRow dtRow in dt.Rows)
            {
                CCandidateDrive objCUser = new CCandidateDrive();

                if (ColContain(dt, dtRow, "candidate_drive_id"))
                    objCUser.CandidateDriveId = Convert.ToInt64(dtRow["candidate_drive_id"]);
                if (ColContain(dt, dtRow, "organization_id"))
                    objCUser.OrganizationId = Convert.ToInt32(dtRow["organization_id"]);
                if (ColContain(dt, dtRow, "candidate_drive_desc"))
                    objCUser.CandidateDriveDesc = dtRow["candidate_drive_desc"].ToString();
                if (ColContain(dt, dtRow, "candidate"))
                    objCUser.Candidate = Convert.ToInt32(dtRow["candidate"]);
                if (ColContain(dt, dtRow, "vacancy"))
                    objCUser.Vacancy = Convert.ToInt32(dtRow["vacancy"]);
                if (ColContain(dt, dtRow, "department"))
                    objCUser.Department = Convert.ToInt32(dtRow["department"]);
                if (ColContain(dt, dtRow, "start_date"))
                    objCUser.StartDate = Convert.ToDateTime(dtRow["start_date"]);
                if (ColContain(dt, dtRow, "end_date"))
                    objCUser.EndDate = Convert.ToDateTime(dtRow["end_date"]);
                if (ColContain(dt, dtRow, "status"))
                    objCUser.Status = Convert.ToInt32(dtRow["status"]);
                if (ColContain(dt, dtRow, "rounds"))
                    objCUser.Rounds = dtRow["rounds"].ToString();

                if (ColContain(dt, dtRow, "is_soft_locked"))
                    objCUser.IsSoftLocked = Convert.ToBoolean(dtRow["is_soft_locked"]);
                if (ColContain(dt, dtRow, "soft_locked_by"))
                    objCUser.SoftLockedBy = Convert.ToInt32(dtRow["soft_locked_by"]);
                if (ColContain(dt, dtRow, "soft_lock_timestamp"))
                    objCUser.SoftLockTimestamp = Convert.ToDateTime(dtRow["soft_lock_timestamp"]);
                if (ColContain(dt, dtRow, "is_hard_locked"))
                    objCUser.IsHardLocked = Convert.ToBoolean(dtRow["is_hard_locked"]);
                if (ColContain(dt, dtRow, "hard_locked_by"))
                    objCUser.HardLockedBy = Convert.ToInt32(dtRow["hard_locked_by"]);
                if (ColContain(dt, dtRow, "hard_lock_timestamp"))
                    objCUser.HardLockTimestamp = Convert.ToDateTime(dtRow["hard_lock_timestamp"]);
                if (ColContain(dt, dtRow, "create_timestamp"))
                    objCUser.CreateTimestamp = Convert.ToDateTime(dtRow["create_timestamp"]);
                if (ColContain(dt, dtRow, "created_by"))
                    objCUser.CreatedBy = Convert.ToInt32(dtRow["created_by"]);
                if (ColContain(dt, dtRow, "modified_timestamp"))
                    objCUser.ModifiedTimestamp = Convert.ToDateTime(dtRow["modified_timestamp"]);
                if (ColContain(dt, dtRow, "modified_by"))
                    objCUser.ModifiedBy = Convert.ToInt32(dtRow["modified_by"]);
                if (ColContain(dt, dtRow, "delete_flag"))
                    objCUser.DeleteFlag = Convert.ToBoolean(dtRow["delete_flag"]);

                lstCUser.Add(objCUser);

            }
            return lstCUser;
        }
    }
}